import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.lang.*;
import java.io.*;
import java.sql.Date;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.collections.*;

public class CommitRation 
{
	//private String url = "jdbc:mysql://localhost:3306/";
	private String url = "jdbc:mysql://warburg.cs.ualberta.ca:3306/";
	private String dbName = "USTC_Hucat_DB";
	private String driverName = "com.mysql.jdbc.Driver";
	private String userName = "whu4";
	private String passwd = "yz62658818";
	
	public String queryDate = null;
	public String queryFile = null;
	public String queryBeforeFile = null;
	
	private Connection conn = null;
	private Statement st = null;
	
	CommitRation()
	{
		queryDate = new String("select min(committerDate) from advChangeBody where project = ?");
		queryFile = new String("select count(distinct line) from advChangeBody where project = ?");
		queryBeforeFile = new String("select count(distinct line) from advChangeBody where project = ? and committerDate < ?");
	}
	
	//open connection
	public void connect() throws ClassNotFoundException, IllegalAccessException, 
		SQLException 
	{
		try
		{
			Class.forName(driverName).newInstance();
			conn = DriverManager.getConnection(url + dbName, userName, passwd);
			System.out.println("Connected to the database");
			
			st = conn.createStatement();
						
			//st.executeUpdate("DROP DATABASE if EXISTS " + dbName);
			//st.executeUpdate("CREATE DATABASE " + dbName);
			//st.executeUpdate("USE " + dbName);
			
			//speak utf-8
			st.executeUpdate("set names utf8");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	//close connection
	public void close() throws SQLException
	{
		try
		{
			conn.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		System.out.println("Disconnected from database");
	}
	
	public PreparedStatement getPreparedStatement(String statement) 
		throws SQLException
	{
		conn.setAutoCommit(false);
		
		PreparedStatement ps = null;
		
		try
		{
			
			ps = conn.prepareStatement(statement);
		}
		catch(Exception e)
		{
			e.printStackTrace();			
		}
		
		return ps;
	}
	
	public void getProjFileNum(String proj2ProjMapFile, String projFileNumMapFile) 
		throws ClassNotFoundException, IllegalAccessException, SQLException, IOException
	{
		connect();
		
		MultiHashMap proj2ProjMap = new MultiHashMap();
		Utilities.readMultiMapFromFile(proj2ProjMap, proj2ProjMapFile);
		
		HashSet<String> removedProjSet = new HashSet<String>();
		HashSet<String> projSet = new HashSet<String>();
		
		for (String proj: (Set<String>)proj2ProjMap.keySet())
		{	
			projSet.add(proj);
			
			for (String basedProj: (Collection<String>)proj2ProjMap.get(proj))
			{
				projSet.add(basedProj);
			}
		}
		
		HashMap<String, String> projFileNumMap = new HashMap<String, String>();
		HashMap<String, Integer> projDataMap = new HashMap<String, Integer>();
		
		for (String proj: projSet)
		{
			PreparedStatement qdps = getPreparedStatement(queryFile);
			qdps.setString(1, proj.replace('/', '_'));
			
			ResultSet rs = qdps.executeQuery();

			while (rs.next())
			{
				
				int fileNum = rs.getInt(1);
				if (fileNum <= 0)
				{
					removedProjSet.add(proj);
					
					break;
				}
				else
				{
					projFileNumMap.put(proj, Integer.toString(fileNum));
					
					break;
				}
				
			}
			
		}
		
		for (String proj: (Set<String>)proj2ProjMap.keySet())
		{
			for (String basedProj: (Collection<String>) proj2ProjMap.getCollection(proj))
			{
				if (removedProjSet.contains(proj) == false
						&& removedProjSet.contains(basedProj) == true)
				{
					System.out.println(proj + ": " + basedProj);
				}
			}
		}
		
	
		Utilities.writeMapToFile(projFileNumMap, projFileNumMapFile);
				
		close();
	}
	
	public void getProjDate(String proj2ProjMapFile, String projFileNumMapFile, String projDateMapFile) 
	throws ClassNotFoundException, IllegalAccessException, SQLException, IOException
	{
		connect();
		
		MultiHashMap proj2ProjMap = new MultiHashMap();
		Utilities.readMultiMapFromFile(proj2ProjMap, proj2ProjMapFile);
		
		HashMap<String, String> projFileNumMap = new HashMap<String, String>();
		Utilities.readMapFromFile(projFileNumMap, projFileNumMapFile);
		
		HashSet<String> projSet = new HashSet<String>();
		
		for (String proj: (Set<String>)projFileNumMap.keySet())
		{	
			projSet.add(proj);

		}
		
		HashMap<String, Date> projDataMap = new HashMap<String, Date>();
		
		for (String proj: projSet)
		{
			PreparedStatement qdps = getPreparedStatement(queryDate);
			qdps.setString(1, proj.replace('/', '_'));
			
			ResultSet rs = qdps.executeQuery();

			//System.out.println(proj.replace('/', '_'));

			while (rs.next())
			{
				
				Date date = rs.getDate(1);
				projDataMap.put(proj, date);
				
			}
			
		}
		
		
		HashMap<String, String> projFileRatioMap = new HashMap<String, String>();
		
		for (String proj: (Set<String>)proj2ProjMap.keySet())
		{
			if (projDataMap.containsKey(proj) == false)
			{
				continue;
			}
			
			for (String basedProj: (Collection<String>)proj2ProjMap.getCollection(proj))
			{
				if (projDataMap.containsKey(basedProj) == false)
				{
					continue;
				}
				
				Date projDate = projDataMap.get(proj);
								
				PreparedStatement qdps = getPreparedStatement(queryBeforeFile);
				qdps.setString(1, basedProj.replace('/', '_'));
				qdps.setDate(2, projDate);
				
				ResultSet rs = qdps.executeQuery();
				
				while (rs.next())
				{
					
					int fileNum = rs.getInt(1);
					if (fileNum <= 0)
					{
						
						break;
					}
					else
					{
						double ratio = ((double)fileNum) / ((double) Integer.parseInt(projFileNumMap.get(basedProj)));
								
						System.out.println(proj + ", " + basedProj + ", " + ratio);
						
						break;
					}
					
				}
			}
		}
				
		close();
	}
	
	public static void main(String args[]) 
		throws ClassNotFoundException, IllegalAccessException, SQLException, IOException
	{
		CommitRation fuyun = new CommitRation();
		fuyun.getProjFileNum("data/android-4.0.1/proj2ProjMap.csv", 
				"data/android-4.0.1/projFileNumMap.csv");
		
		//fuyun.getProjDate("data/android-4.0.1/proj2ProjMap.csv", 
		//"data/android-4.0.1/projFileNumMap.csv",
		//"aaa");
		
	}
}
